﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using DTO;
namespace DAO
{
    public class GiaoHangDAO
    {
        #region GiaoHang
        public static void InsertGiaoHang(GiaoHangDTO giaohangDTO)
        {
            try
            {
                String strSql = "insert into GiaoHang(Ngay, TaiXe) values(?, ?)";
                String connStr = DataAccess.GetConnString();
                OleDbConnection conn = new OleDbConnection(connStr);
                conn.Open();
                OleDbCommand com = new OleDbCommand(strSql, conn);

                com.Parameters.Add("@Ngay", OleDbType.Date);
                com.Parameters.Add("@TaiXe", OleDbType.VarWChar);

                com.Parameters["@Ngay"].Value = giaohangDTO.Ngay;
                com.Parameters["@TaiXe"].Value = giaohangDTO.TaiXe;

                com.ExecuteNonQuery();

                strSql = "Select @@IDENTITY as ID";
                com = new OleDbCommand(strSql, conn);
                giaohangDTO.Id = int.Parse(com.ExecuteScalar().ToString());
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static void UpdateGiaoHang(GiaoHangDTO giaohangDTO)
        {
            try
            {
                String strSql = "update GiaoHang set Ngay=?, TaiXe=?, HoanThanh=? where ID=?";
                String connStr = DataAccess.GetConnString();
                OleDbConnection conn = new OleDbConnection(connStr);
                conn.Open();
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Parameters.Add("@Ngay", OleDbType.Date);
                com.Parameters.Add("@TaiXe", OleDbType.VarWChar);
                com.Parameters.Add("@HoanThanh", OleDbType.Boolean);
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@Ngay"].Value = giaohangDTO.Ngay;
                com.Parameters["@TaiXe"].Value = giaohangDTO.TaiXe;
                com.Parameters["@HoanThanh"].Value = giaohangDTO.HoanThanh;
                com.Parameters["@ID"].Value = giaohangDTO.Id;
                com.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex) { throw ex; }
        }
        public static void UpdateGiaoHang(OleDbTransaction tran, GiaoHangDTO giaohangDTO)
        {
            try
            {
                String strSql = "update GiaoHang set Ngay=?, TaiXe=?, HoanThanh=? where ID=?";
                OleDbConnection conn = DataAccess.OpenOleConnection();                
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@Ngay", OleDbType.Date);
                com.Parameters.Add("@TaiXe", OleDbType.VarWChar);
                com.Parameters.Add("@HoanThanh", OleDbType.Boolean);
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@Ngay"].Value = giaohangDTO.Ngay;
                com.Parameters["@TaiXe"].Value = giaohangDTO.TaiXe;
                com.Parameters["@HoanThanh"].Value = giaohangDTO.HoanThanh;
                com.Parameters["@ID"].Value = giaohangDTO.Id;
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
        }
        public static void DeleteGiaoHang(int ID)
        {
            try
            {
                String strSql = "delete from GiaoHang where ID=?";
                String connStr = DataAccess.GetConnString();
                OleDbConnection conn = new OleDbConnection(connStr);
                conn.Open();
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@ID"].Value = ID;
                com.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex) { throw ex; }
        }
        public static void DeleteGiaoHang(OleDbTransaction tran, int ID)
        {
            try
            {
                String strSql = "delete from GiaoHang where ID=?";                
                OleDbConnection conn = DataAccess.OpenOleConnection();                
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@ID"].Value = ID;
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
        }
        #endregion

        #region Chi tiet xe di
        public static void InsertChiTietXeDi(ChiTietXeDi chiTietXeDi)
        {
            try
            {
                String strSql = "insert into ChiTietXeDi(MaGiaoHang, LoaiGas, SoLuong, SoLuongGiao) values(?, ?,?)";
                String connStr = DataAccess.GetConnString();
                OleDbConnection conn = new OleDbConnection(connStr);
                conn.Open();
                OleDbCommand com = new OleDbCommand(strSql, conn);

                com.Parameters.Add("@MaGiaoHang", OleDbType.Integer);
                com.Parameters.Add("@LoaiGas", OleDbType.Integer);
                com.Parameters.Add("@SoLuong", OleDbType.Integer);
                com.Parameters.Add("@SoLuongGiao", OleDbType.Integer);

                com.Parameters["@MaGiaoHang"].Value = chiTietXeDi.MaGiaoHang;
                com.Parameters["@LoaiGas"].Value = chiTietXeDi.MaLoaiGas;
                com.Parameters["@SoLuong"].Value = chiTietXeDi.SoLuong;
                com.Parameters["@SoLuongGiao"].Value = chiTietXeDi.SoLuongGiao;

                com.ExecuteNonQuery();

                strSql = "Select @@IDENTITY as ID";
                com = new OleDbCommand(strSql, conn);
                chiTietXeDi.ID = int.Parse(com.ExecuteScalar().ToString());

                
                conn.Close();
            }
            catch (Exception ex)
            {

                throw;
            }
        }
        public static ChiTietXeDi Tim(OleDbTransaction tran, int maGiaoHang, int loaiGas)
        {
            String strSql = "select* from ChiTietXeDi where MaGiaoHang=" + maGiaoHang + " and LoaiGas=" + loaiGas;
            DataTable dt = DataAccess.Query(tran, strSql);            
            if (dt.Rows.Count > 0)
            {
                ChiTietXeDi chiTiet = new ChiTietXeDi();
                chiTiet.ID = int.Parse(dt.Rows[0]["ID"].ToString());
                chiTiet.MaGiaoHang = int.Parse(dt.Rows[0]["MaGiaoHang"].ToString());
                chiTiet.MaLoaiGas = int.Parse(dt.Rows[0]["LoaiGas"].ToString());
                chiTiet.SoLuongGiao = int.Parse(dt.Rows[0]["SoLuongGiao"].ToString());
                chiTiet.SoLuong = int.Parse(dt.Rows[0]["SoLuong"].ToString());
                return chiTiet;
            }
            return null;
        }
        public static void InsertChiTietXeDi(OleDbTransaction tran, ChiTietXeDi chiTietXeDi)
        {
            try
            {
                ChiTietXeDi chiTiet = Tim(tran, chiTietXeDi.MaGiaoHang, chiTietXeDi.MaLoaiGas);
                if (chiTiet != null)
                {
                    chiTiet.SoLuong += chiTietXeDi.SoLuong;
                    chiTiet.SoLuongGiao = chiTiet.SoLuong;
                    UpdateChiTietXeDi(tran, chiTiet);
                }
                else
                {
                    String strSql = "insert into ChiTietXeDi(MaGiaoHang, LoaiGas, SoLuong, SoLuongGiao) values(?,?,?,?)";
                    OleDbConnection conn = DataAccess.OpenOleConnection();
                    OleDbCommand com = new OleDbCommand(strSql, conn);
                    com.Transaction = tran;
                    com.Parameters.Add("@MaGiaoHang", OleDbType.Integer);
                    com.Parameters.Add("@LoaiGas", OleDbType.Integer);
                    com.Parameters.Add("@SoLuong", OleDbType.Integer);
                    com.Parameters.Add("@SoLuongGiao", OleDbType.Integer);

                    com.Parameters["@MaGiaoHang"].Value = chiTietXeDi.MaGiaoHang;
                    com.Parameters["@LoaiGas"].Value = chiTietXeDi.MaLoaiGas;
                    com.Parameters["@SoLuong"].Value = chiTietXeDi.SoLuong;
                    com.Parameters["@SoLuongGiao"].Value = chiTietXeDi.SoLuongGiao;

                    com.ExecuteNonQuery();

                    strSql = "Select @@IDENTITY as ID";
                    com = new OleDbCommand(strSql, conn);
                    com.Transaction = tran;
                    chiTietXeDi.ID = int.Parse(com.ExecuteScalar().ToString());
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        public static void UpdateChiTietXeDi(ChiTietXeDi chiTietXeDi)
        {
            try
            {
                String strSql = "update ChiTietXeDi set MaGiaoHang=?, LoaiGas=? SoLuong=? SoLuongGiao=? where ID=?";                
                OleDbConnection conn = DataAccess.OpenOleConnection();                
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Parameters.Add("@MaGiaoHang", OleDbType.Integer);
                com.Parameters.Add("@LoaiGas", OleDbType.Integer);
                com.Parameters.Add("@SoLuong", OleDbType.Integer);
                com.Parameters.Add("@SoLuongGiao", OleDbType.Integer);
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@MaGiaoHang"].Value = chiTietXeDi.MaGiaoHang;
                com.Parameters["@LoaiGas"].Value = chiTietXeDi.MaLoaiGas;
                com.Parameters["@SoLuong"].Value = chiTietXeDi.SoLuong;
                com.Parameters["@SoLuongGiao"].Value = chiTietXeDi.SoLuongGiao;
                com.Parameters["@ID"].Value = chiTietXeDi.ID;
                com.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex) { throw ex; }
        }
        public static void UpdateChiTietXeDi(OleDbTransaction tran,ChiTietXeDi chiTietXeDi)
        {
            try
            {
                String strSql = "update ChiTietXeDi set MaGiaoHang=?, LoaiGas=?, SoLuong=? , SoLuongGiao=? where ID=?";
                OleDbConnection conn = DataAccess.OpenOleConnection();                
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@MaGiaoHang", OleDbType.Integer);
                com.Parameters.Add("@LoaiGas", OleDbType.Integer);
                com.Parameters.Add("@SoLuong", OleDbType.Integer);
                com.Parameters.Add("@SoLuongGiao", OleDbType.Integer);
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@MaGiaoHang"].Value = chiTietXeDi.MaGiaoHang;
                com.Parameters["@LoaiGas"].Value = chiTietXeDi.MaLoaiGas;
                com.Parameters["@SoLuong"].Value = chiTietXeDi.SoLuong;
                com.Parameters["@SoLuongGiao"].Value = chiTietXeDi.SoLuongGiao;
                com.Parameters["@ID"].Value = chiTietXeDi.ID;
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
        }
        public static void DeleteChiTietXeDi(int id)
        {
            try
            {
                String strSql = "delete from ChiTietXeDi where ID=?";
                String connStr = DataAccess.GetConnString();
                OleDbConnection conn = new OleDbConnection(connStr);
                conn.Open();
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@ID"].Value = id;
                com.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex) { throw ex; }
        }
        public static void DeleteChiTietXeDi(OleDbTransaction tran, int id)
        {
            try
            {
                String strSql = "delete from ChiTietXeDi where ID=?";                
                OleDbConnection conn = DataAccess.OpenOleConnection();                
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@ID"].Value = id;
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
        } 
        #endregion
        #region chi tiet xe ve
        public static void InsertChiTietXeVe(ChiTietXeVe chiTietXeVe)
        {
            try
            {
                String strSql = "insert into ChiTietXeVe(MaGiaoHang, LoaiGas, SoLuong) values(?, ?,?)";
                String connStr = DataAccess.GetConnString();
                OleDbConnection conn = new OleDbConnection(connStr);
                conn.Open();
                OleDbCommand com = new OleDbCommand(strSql, conn);

                com.Parameters.Add("@MaGiaoHang", OleDbType.Integer);
                com.Parameters.Add("@LoaiGas", OleDbType.Integer);
                com.Parameters.Add("@SoLuong", OleDbType.Integer);

                com.Parameters["@MaGiaoHang"].Value = chiTietXeVe.MaGiaoHang;
                com.Parameters["@LoaiGas"].Value = chiTietXeVe.MaLoaiGas;
                com.Parameters["@SoLuong"].Value = chiTietXeVe.SoLuong;

                com.ExecuteNonQuery();

                strSql = "Select @@IDENTITY as ID";
                com = new OleDbCommand(strSql, conn);
                chiTietXeVe.ID = int.Parse(com.ExecuteScalar().ToString());
                conn.Close();
            }
            catch (Exception ex)
            {

                throw;
            }
        }
        public static void UpdateChiTietXeVe(ChiTietXeVe chiTietXeVe)
        {
            try
            {
                String strSql = "update ChiTietXeVe set MaGiaoHang=?, LoaiGas=? SoLuong=? where ID=?";
                String connStr = DataAccess.GetConnString();
                OleDbConnection conn = new OleDbConnection(connStr);
                conn.Open();
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Parameters.Add("@MaGiaoHang", OleDbType.Integer);
                com.Parameters.Add("@LoaiGas", OleDbType.Integer);
                com.Parameters.Add("@SoLuong", OleDbType.Integer);
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@MaGiaoHang"].Value = chiTietXeVe.MaGiaoHang;
                com.Parameters["@LoaiGas"].Value = chiTietXeVe.MaLoaiGas;
                com.Parameters["@SoLuong"].Value = chiTietXeVe.SoLuong;
                com.Parameters["@ID"].Value = chiTietXeVe.ID;
                com.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex) { throw ex; }
        }
        public static void DeleteChiTietXeVe(int id)
        {
            try
            {
                String strSql = "delete from ChiTietXeVe where ID=?";
                String connStr = DataAccess.GetConnString();
                OleDbConnection conn = new OleDbConnection(connStr);
                conn.Open();
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@ID"].Value = id;
                com.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex) { throw ex; }
        } 
        #endregion
    }
}

